[Redshift] ウィンドウ関数：SUMを使用して合計値と累積値を作成する

#データベース

takahara

2016.12.07

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

はじめに

関数SUMはよく使いますが、ウィンドウ関数にもSUMが存在します。実際に使ってみて合計値と累積値を作成してみます。

環境

Mac OSX 10.10.5
Redshift 1.0.1125

テーブルの準備

商品の販売データ：fruit_sales_data

CREATE SCHEMA IF NOT EXISTS blog;

CREATE TABLE blog.fruit_sales_data (
  id INTEGER
  , group_id INTEGER
  , name VARCHAR(8)
  , price INTEGER
  , quantity INTEGER
);

INSERT INTO blog.fruit_sales_data VALUES
(1,1,'apple',300,5)
,(2,1,'apple',300,10)
,(3,2,'orange',200,7)
,(4,2,'orange',200,15)
,(5,3,'banana',100,2)
,(6,3,'banana',100,9)
,(7,2,'orange',200,10)
,(8,3,'banana',100,11);

.
 id | group_id |  name  | price | quantity 
----+----------+--------+-------+----------
  1 |        1 | apple  |   300 |        5
  2 |        1 | apple  |   300 |       10
  3 |        2 | orange |   200 |        7
  4 |        2 | orange |   200 |       15
  5 |        3 | banana |   100 |        2
  6 |        3 | banana |   100 |        9
  7 |        2 | orange |   200 |       10
  8 |        3 | banana |   100 |       11
(8 rows)

左から、レコードID、商品ID、商品名、価格、販売数となっています。

構文

Amazon Redshift | ウィンドウ関数：SUM

SUM ( [ ALL ] expression ) OVER ([ PARTITION BY expr_list ] 
                                 [ ORDER BY order_list frame_clause ])

句・引数	説明
expression	関数の対象となる列または式。
ALL	引数 ALL を指定すると、この関数は式から重複する値をすべて保持します。ALL がデフォルト。
OVER	集計関数に使用するウィンドウ句を指定します。OVER 句は、ウィンドウ集計関数を標準セット集計関数と区別します。
PARTITION BY expr_list	1 つ以上の式で SUM 関数のウィンドウを定義します。
ORDER BY order_list	各パーティション内の行をソートします。PARTITION BY が指定されていない場合、ORDER BY はテーブル全体を使用します。
frame_clause	ORDER BY 句が集計関数に使用される場合、明示的なフレーム句が必要です。フレーム句は順序付けた結果内の行のセットを含めるか除外して、関数のウィンドウの行のセットを絞り込みます。フレーム句は ROWS キーワードおよび関連する指定子で構成されます。 Amazon Redshift \| ウィンドウ関数の構文の概要

使い方

使い方１：商品グループごとの合計値のカラムを追加

SQL

SELECT 
  name
  , quantity
  , SUM(quantity) OVER(PARTITION BY name) 
FROM blog.fruit_sales_data 
GROUP BY name, quantity
ORDER BY name;

実行結果

.
  name  | quantity | sum 
--------+----------+-----
 apple  |        5 |  15
 apple  |       10 |  15
 banana |        2 |  22
 banana |       11 |  22
 banana |        9 |  22
 orange |       10 |  32
 orange |        7 |  32
 orange |       15 |  32
(8 rows)

使い方２：行ごとに販売額と総販売額のカラムを追加

SQL

SELECT
  name
  , price
  , quantity
  , price * quantity AS group_price
  , SUM(price * quantity) OVER() AS total
FROM blog.fruit_sales_data
GROUP BY name, quantity, price
ORDER BY name, quantity;

実行結果

.
  name  | price | quantity | group_price | total 
--------+-------+----------+-------------+-------
 apple  |   300 |        5 |        1500 | 13100
 apple  |   300 |       10 |        3000 | 13100
 banana |   100 |        2 |         200 | 13100
 banana |   100 |        9 |         900 | 13100
 banana |   100 |       11 |        1100 | 13100
 orange |   200 |        7 |        1400 | 13100
 orange |   200 |       10 |        2000 | 13100
 orange |   200 |       15 |        3000 | 13100
(8 rows)

使い方３：商品グループごとの累積値のカラムを追加

使い方１と似ていますが、ORDER BY句などのオプションの設定で実現しています。
「UNBOUNDED PRECEDING」は、前にある行を全て」という意味です。
他にも色々指定ができます。

SQL

SELECT
  name
  , quantity
  , SUM(quantity) OVER(PARTITION BY name ORDER BY quantity ROWS UNBOUNDED PRECEDING)
FROM blog.fruit_sales_data
ORDER BY name;

実行結果

.
  name  | quantity | sum 
--------+----------+-----
 apple  |        5 |   5
 apple  |       10 |  15
 banana |        2 |   2
 banana |        9 |  11
 banana |       11 |  22
 orange |        7 |   7
 orange |       10 |  17
 orange |       15 |  32
(8 rows)